package eu.jba.denshijisho.dao;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import eu.jba.denshijisho.R;
import eu.jba.denshijisho.entity.Card;

public class CardDao extends AbstractDao<Card> {

	public static final String KEY_ROWID = "_id";
	public static final String KEY_ROW_ID_NAME = "id";
	public static final String KEY_SVK_WORD = "svkWord";
	public static final String KEY_SVK_WORD_NAME = "Slovak Word";
	public static final String KEY_KANA_WORD = "kanaWord";
	public static final String KEY_NAME_WORD_NAME = "Kana Word";
	public static final String KEY_KANJI = "kanji";
	public static final String KEY_KANJI_NAME = "Kanji";
	public static final String KEY_TYPE = "type";
	public static final String KEY_TYPE_NAME = "Type";
	public static final String KEY_STAT_ID = "statId";
	public static final String KEY_STAT_ID_NAME = "Stat Id";

	public CardDao(Context ctx) {
		super(ctx);
	}

	public int findSimilar(String svkWord, String kanaWord, String kanji) {
		final StringBuilder sb = new StringBuilder();
		sb.append("svkWord like ?").append(" or ").append("kanaWord like ?");
		if (!kanji.equals("")) {
			sb.append(" or ").append("kanji like ?");
		}
		Cursor cursor = null;

		try {
			cursor = getDb().query(true, getTableName(), getColumns(),
					sb.toString(), 
					!kanji.equals("") ? new String[] { svkWord +"%", kanaWord + "%", kanji + "%" } : 
										new String[] { svkWord +"%", kanaWord + "%"},
					null, null, null, null

			);
			
			return cursor.getCount();
		} finally {
			if (cursor != null) {
				cursor.close();
			}
		}
	}

	public List<Card> search(String query) {
		final StringBuilder sb = new StringBuilder();
		sb.append("svkWord like ?").append(" or ").append("kanaWord like ?");
		Cursor cursor = null;

		try {
			cursor = getDb().query(true, getTableName(), getColumns(),
					sb.toString(), 
					new String[] { query +"%", query + "%" },
					null, null, null, null

			);
			final List<Card> list = new ArrayList<Card>();
			while (cursor.moveToNext()) {
				list.add(convertTo(cursor));
			}

			return list;
		} finally {
			if (cursor != null) {
				cursor.close();
			}
		}
	}

	public List<Card> getCardsByType( String type){
		Cursor cursor = null;
		try {
			cursor = getDb().query(true, getTableName(), getColumns(),
					KEY_TYPE + " = '" + type + "'", null, null, null, null,
					null);
			final List<Card> list = new ArrayList<Card>();
			if (cursor != null) {
				cursor.moveToFirst();
			}
			list.add( convertTo( cursor ) );
			while (cursor.moveToNext()) {
				list.add(convertTo(cursor));
			}

			return list;
			
		} finally {
			if (cursor != null) {
				cursor.close();
			}
		}		
	}
	
	public int getRowCount(String type) {
		Cursor cursor = null;
		try {
			cursor = getDb().query(true, getTableName(), getColumns(),
					KEY_TYPE + " = '" + type + "'", null, null, null, null,
					null);
			if (cursor != null) {
				cursor.moveToFirst();
			}
			return cursor.getCount();
		} finally {
			if (cursor != null) {
				cursor.close();
			}
		}
	}

	@Override
	protected String getTableName() {
		return getContext().getResources().getString(R.database.CARD_TABLE);
	}

	@Override
	protected ContentValues getInitialValues(Card card) {
		final ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_SVK_WORD, card.getSvkWord());
		initialValues.put(KEY_KANA_WORD, card.getKanaWord());
		initialValues.put(KEY_KANJI, card.getKanji());
		initialValues.put(KEY_TYPE, card.getType());
		initialValues.put(
			KEY_STAT_ID, card.getStat() != null ? card.getStat().getId() : card.getStatId() );
		return initialValues;
	}

	@Override
	protected String getRowIdColumnName() {
		return KEY_ROWID;
	}

	@Override
	protected String[] getColumns() {
		return new String[] { KEY_ROWID, KEY_SVK_WORD, KEY_KANA_WORD,
				KEY_KANJI, KEY_TYPE, KEY_STAT_ID };
	}

	@Override
	protected Card convertTo(Cursor cursor) {
		final Card card = new Card(cursor.getInt(0), cursor.getString(1),
				cursor.getString(2), cursor.getString(3), cursor.getString(4),
				cursor.getInt(5));
		return card;
	}

	@Override
	public Card get(long rowId) {
		return super.get(rowId);
	}

	@Override
	protected int getCreateResource() {
		return R.raw.create_table_card;
	}

	@Override
	protected int getUpdateResource() {
		return R.raw.update_table_card;
	}

	public static String[] getColumnNames() {
		return new String[] { KEY_ROW_ID_NAME, KEY_SVK_WORD_NAME,
				KEY_NAME_WORD_NAME, KEY_KANJI_NAME, KEY_TYPE_NAME,
				KEY_STAT_ID_NAME };
	}
}
